package com.sq.partner.manager.dao.impl;

import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.INlifeRiskDao;
import com.sq.partner.manager.mybatis.model.NlifeCoreProdRisk;
import com.sq.partner.manager.mybatis.model.NlifeRisk;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;

@Repository
public class NlifeRiskDaoImpl extends BaseDaoImpl implements INlifeRiskDao {
	private static Logger logger = LogUtil.getLog(NlifeRiskDaoImpl.class);

	@Override
	public Page<NlifeCoreProdRisk> queryCoreProdRiskTempletPageById(Page<NlifeCoreProdRisk> page, String coreProdCode, String prodCode, String riskCode, String riskName) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			String sql = "SELECT T.CORE_PROD_CODE,T2.CORE_PROD_NAME,T1.CORE_RISK_CODE,T1.CORE_RISK_NAME,T1.RISK_TYPE,"
					+ " T1.PRMRY_FLAG FROM T_NLIFE_CORE_PROD_RISK T INNER JOIN T_NLIFE_CORE_RISK T1"
					+ " ON T.CORE_RISK_CODE = T1.CORE_RISK_CODE INNER JOIN T_NLIFE_CORE_PROD T2"
					+ " ON T.CORE_PROD_CODE = T2.CORE_PROD_CODE  WHERE T.CORE_PROD_CODE = ?"
					+ " ORDER BY T1.CORE_RISK_CODE ASC";
			if (!StrUtil.isEmpty(riskCode) && !StrUtil.isEmpty(riskName)){
				sql = "SELECT * FROM (" + sql + ") R WHERE R.CORE_RISK_CODE LIKE '%' || ? || '%'"
						+ " AND R.CORE_RISK_NAME LIKE '%' || ? || '%'";
			} else if (!StrUtil.isEmpty(riskCode)) {
				sql = "SELECT * FROM (" + sql + ") R WHERE R.CORE_RISK_CODE LIKE '%' || ? || '%'";
			} else if (!StrUtil.isEmpty(riskName)) {
				sql = "SELECT * FROM (" + sql + ") R WHERE R.CORE_RISK_NAME LIKE '%' || ? || '%'";
			}
			logger.debug("执行SQL：" + sql);
			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			pstmt.setObject(1, coreProdCode);
			if (!StrUtil.isEmpty(riskCode) && !StrUtil.isEmpty(riskName)){
				pstmt.setObject(2, riskCode);
				pstmt.setObject(3, riskName);
			} else if (!StrUtil.isEmpty(riskCode)) {
				pstmt.setObject(2, riskCode);
			} else if (!StrUtil.isEmpty(riskName)) {
				pstmt.setObject(2, riskName);
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			pstmt1.setObject(1, coreProdCode);
			if (!StrUtil.isEmpty(riskCode) && !StrUtil.isEmpty(riskName)){
				pstmt1.setObject(2, riskCode);
				pstmt1.setObject(3, riskName);
			} else if (!StrUtil.isEmpty(riskCode)) {
				pstmt1.setObject(2, riskCode);
			} else if (!StrUtil.isEmpty(riskName)) {
				pstmt1.setObject(2, riskName);
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public Page<NlifeRisk> queryRiskPageByCriteria(Page<NlifeRisk> page,String prodCode) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			String sql = "SELECT T.PROD_CODE,T.CORE_RISK_CODE,T.RISK_NAME,T.PRMRY_FLAG, (case T.Prmry_Flag  when '0' then 1.5 else TO_NUMBER(T.PRMRY_FLAG)  end) de  FROM T_NLIFE_RISK T"
					+ " WHERE T.PROD_CODE = ? ORDER BY de desc";
			logger.debug("执行SQL：" + sql);
			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			pstmt.setObject(1, prodCode);
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			pstmt1.setObject(1, prodCode);
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public boolean saveRisk(NlifeRisk risk) throws Exception {
		return this.add("insertNlifeRisk",risk);
	}

	@Override
	public boolean updateRisk(NlifeRisk risk) throws Exception {
		return this.update("updateNlifeRiskNotNull", risk);
	}

	@Override
	public boolean deleteRisk(NlifeRisk risk) throws Exception {
		return this.delete("delNlifeRiskById", risk);
	}

	@Override
	public boolean deleteRiskInfoByProdCode(String prodCode) throws Exception {
		return this.delete("delNlifeRiskByProdCode", prodCode);
	}

	@Override
	public boolean queryIsExistRiskPrimaryKeyByParams(NlifeRisk risk) throws Exception {
		risk = (NlifeRisk) this.query("queryNlifeRiskByMap", risk);
		if(null!=risk){
			return true;
		}
		return false;
	}

	@Override
	public boolean updateMRisk(String prodCode, String tradRiskCode,
							   Integer flag,String rationId) throws Exception {
		Map<String, String> map=new HashMap<String, String>();
		map.put("prmryFlag", "0");
		map.put("prodCode", prodCode);
		map.put("coreRiskCode", tradRiskCode);
		map.put("rationId", rationId);
		this.update("updateNlifeRisk2", map);
		map.put("prmryFlag", "2");
		return this.update("updateNlifeRisk1", map);
	}

	@Override
	public Integer queryRiskType(Map<String, String> map) throws SQLException {
		int a = (Integer) this.query("queryRiskTypeByMap", map);
		return a;
	}

	@Override
	public List<Map<String, Object>> impNlifeRisk(Map<String, Object>map)
			throws SQLException {
		// TODO Auto-generated method stub
		return this.queryList("impNlifeRiskByMap",map);
	}

	//激活卡激活
	/**
	 * 根据投保方案，查询险种保额
	 * @param nlifePlanNO
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String,Object>> queryNlifeRiskAmt(String nlifePlanNo)
			throws Exception {
		List<Map<String,Object>> list = this.queryList("queryNlifeRiskAmt", nlifePlanNo);
		if(list.get(0).get("SPECIAL_ARRAN")!=null){
			String SPECIAL_ARRAN = StrUtil.ClobToString((Clob) list.get(0).get("SPECIAL_ARRAN"));
			for (Map<String, Object> map : list) {
				map.put("SPECIAL_ARRAN", SPECIAL_ARRAN);
			}
		}
		return list;
	}



	@SuppressWarnings("unchecked")
	@Override
	public List<NlifeRisk> queryNlifeRiskByParam(Map<String, Object> map)
			throws Exception {
		return this.queryList("queryNlifeRiskByParam", map);
	}

	@Override
	public boolean queryNlifeRiskIsExits(NlifeRisk risk) throws Exception {
		int count = (Integer) this.query("queryNlifeRiskIsExits", risk);
		if(0!=count){
			return true;
		}
		return false;
	}

	@Override
	public Integer queryMainRiskCount(String prodCode) throws Exception {
		int count=(Integer)this.query("queryMainRiskCount",prodCode);
		return count;
	}
	@Override
	public List<NlifeRisk> queryMainRisk(String prodCode) throws Exception {
		return (List<NlifeRisk>) this.queryList("queryMainRisk", prodCode);
	}

	public NlifeRisk queryFirstRisk(String prodCode) throws Exception {
		return (NlifeRisk) this.query("queryFirstRisk",prodCode);
	}
}
